IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_LineProblemsByResultAndNote')
	BEGIN
		DROP  Procedure  up_Report_LineProblemsByResultAndNote
	END

GO

CREATE Procedure up_Report_LineProblemsByResultAndNote
	@ProjectId int = null
AS

--HANDLE OLD VERSION
IF (@ProjectId IS NULL)
BEGIN
	SET @ProjectId = 6
END

select 
	N.Vendor_InspVisit_XmissionStructures_NoteId,
	N.Name as noteName,
	IP.Vendor_InspVisit_XMissionStructure_PoleId,
	RP.Comments
from tb_Vendor_InspVisit_XmissionStructures_Poles IP 
LEFT join tb_Utility_Asset_XMissionStructures_Poles AP
	ON AP.Utility_Asset_XMissionStructures_PoleId = IP.Utility_Asset_XMissionStructures_PoleFk
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_Results RP
	ON IP.Vendor_InspVisit_XMissionStructure_PoleId = RP.Vendor_InspVisit_XMissionStructure_PoleFk
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_ResultsXNotes RN
	ON RP.Vendor_InspVisit_XmissionStructures_ResultId = RN.Vendor_InspVisit_XmissionStructures_ResultFk
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_Notes N
	ON RN.Vendor_InspVisit_XmissionStructures_NoteFk = N.Vendor_InspVisit_XmissionStructures_NoteId
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_ResultTypes RT
	ON RT.Vendor_InspVisit_XmissionStructures_ResultTypeId = RP.Vendor_InspVisit_XmissionStructures_ResultTypeFk
	
WHERE IP.Vendor_ProjectFK = @ProjectId	
	
order by RP.Vendor_InspVisit_XmissionStructures_ResultTypeFk,N.Name

/*
--OLD VERSION
select 
	N.Vendor_InspVisit_XmissionStructures_NoteId,
	N.Name as noteName,
	IP.Vendor_InspVisit_XMissionStructure_PoleId,
	RP.Comments
from tb_Utility_Asset_XMissionStructures_Poles AP
inner join  tb_Vendor_InspVisit_XmissionStructures_Poles IP 
	ON AP.Utility_Asset_XMissionStructures_PoleId = IP.Utility_Asset_XMissionStructures_PoleFk
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_Results RP
	ON IP.Vendor_InspVisit_XMissionStructure_PoleId = RP.Vendor_InspVisit_XMissionStructure_PoleFk
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_ResultsXNotes RN
	ON RP.Vendor_InspVisit_XmissionStructures_ResultId = RN.Vendor_InspVisit_XmissionStructures_ResultFk
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_Notes N
	ON RN.Vendor_InspVisit_XmissionStructures_NoteFk = N.Vendor_InspVisit_XmissionStructures_NoteId
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_ResultTypes RT
	ON RT.Vendor_InspVisit_XmissionStructures_ResultTypeId = RP.Vendor_InspVisit_XmissionStructures_ResultTypeFk
	
WHERE IP.Vendor_ProjectFK = @ProjectId	
	
order by RP.Vendor_InspVisit_XmissionStructures_ResultTypeFk,N.Name
*/






GO

GRANT EXEC ON up_Report_LineProblemsByResultAndNote TO PPIReports

GO

